SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


CREATE PROCEDURE dbo.[DnnProduct_Journal_ListForProfile]
@PortalId int,
@ModuleId int,
@CurrentUserId int,
@ProfileId int,
@RowIndex int,
@MaxRows int
AS
DECLARE @EndRow int
SET @EndRow = @RowIndex + @MaxRows;

DECLARE @j TABLE(id int IDENTITY, journalid int, datecreated datetime)
IF EXISTS(SELECT * from dbo.[DnnProduct_Journal_TypeFilters] WHERE ModuleId = @ModuleId)
INSERT INTO @j 
	SELECT j.journalid, jt.datecreated from (
		SELECT DISTINCT js.JournalId from dbo.[DnnProduct_Journal] as j
			INNER JOIN dbo.[DnnProduct_Journal_Security] as js ON js.JournalId = j.JournalId
			INNER JOIN dbo.[DnnProduct_Journal_User_Permissions](@PortalId,@CurrentUserId ,1) as t ON t.seckey = js.SecurityKey
			WHERE j.ProfileId = @ProfileId AND j.PortalId = @PortalId
		) as j INNER JOIN dbo.[DnnProduct_Journal] jt ON jt.JournalId = j.JournalId AND jt.PortalId = @PortalId
		INNER JOIN dbo.[DnnProduct_Journal_TypeFilters] as jf ON jf.JournalTypeId = jt.JournalTypeId AND jf.ModuleId = @ModuleId
		ORDER BY jt.DateCreated DESC, jt.JournalId DESC;

ELSE
INSERT INTO @j 
	SELECT j.journalid, jt.datecreated from (
		SELECT DISTINCT js.JournalId from dbo.[DnnProduct_Journal] as j
			INNER JOIN dbo.[DnnProduct_Journal_Security] as js ON js.JournalId = j.JournalId
			INNER JOIN dbo.[DnnProduct_Journal_User_Permissions](@PortalId,@CurrentUserId ,1) as t ON t.seckey = js.SecurityKey
			WHERE j.ProfileId = @ProfileId AND j.PortalId = @PortalId
		) as j INNER JOIN dbo.[DnnProduct_Journal] jt ON jt.JournalId = j.JournalId AND jt.PortalId = @PortalId
		ORDER BY jt.DateCreated DESC, jt.JournalId DESC;


WITH journalItems  AS
(
	SELECT	j.JournalId,
			ROW_NUMBER() OVER (ORDER BY j.JournalId DESC) AS RowNumber
	FROM	dbo.[DnnProduct_Journal] as j INNER JOIN @j as jtmp ON jtmp.JournalId = j.JournalId
	WHERE j.PortalId = @PortalId AND j.ProfileId = @ProfileId
)
SELECT	j.JournalId, j.JournalTypeId, j.Title, j.Summary, j.UserId, j.DateCreated, j.DateUpdated, j.PortalId,
			j.ProfileId, j.GroupId, j.ObjectKey, j.AccessKey,
			"JournalOwner" = '<entity><id>' + CAST(p.UserId as nvarchar(150)) + '</id><name><![CDATA[' + p.DisplayName + ']]></name></entity>',
			"JournalAuthor" = CASE WHEN ISNULL(a.UserId,-1) >0 THEN '<entity><id>' + CAST(a.UserId as nvarchar(150)) + '</id><name><![CDATA[' + a.DisplayName + ']]></name></entity>' ELSE '' END,
			"JournalOwnerId" = ISNULL(j.ProfileId,j.UserId),
			 jt.Icon, jt.JournalType,
			"Profile" = CASE WHEN j.ProfileId > 0 THEN '<entity><id>' + CAST(p.UserID as nvarchar(150)) + '</id><name><![CDATA[' + p.DisplayName + ']]></name><vanity></vanity></entity>' ELSE '' END,
			jd.JournalXML, j.ContentItemId, j.ItemData, RowNumber
FROM	journalItems as ji INNER JOIN 
	dbo.[DnnProduct_Journal] as j ON j.JournalId = ji.JournalId INNER JOIN
dbo.[DnnProduct_Journal_Types] as jt ON jt.JournalTypeId = j.JournalTypeId LEFT OUTER JOIN
			dbo.[DnnProduct_Journal_Data] as jd on jd.JournalId = j.JournalId LEFT OUTER JOIN
            dbo.[DnnProduct_Users] AS p ON j.ProfileId = p.UserID LEFT OUTER JOIN
            dbo.[DnnProduct_Users] AS a ON j.UserId = a.UserID
WHERE	RowNumber BETWEEN @RowIndex AND @EndRow
ORDER BY RowNumber ASC;
GO
